package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.AssignedSubjects;

public class AssignedSubjectsMod {
	Connection connection;
	private static AssignedSubjectsMod isntance;
	public static AssignedSubjectsMod getIsntance() {
		if (isntance == null) {
			isntance = new AssignedSubjectsMod();
		}
		return isntance;
	}
	public boolean addAssisgnedSubject(AssignedSubjects assignedSubjects) {
		connection =  DB_Connection.getIsntance().getConnection();
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			sqlr = connection
					.prepareStatement("INSERT INTO assigned_subjects ( uid , sjid) VALUES (?, ?)");
			sqlr.setInt(1, assignedSubjects.getUid());
			sqlr.setInt(2, assignedSubjects.getSjid());
			int number = sqlr.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sqlr.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public boolean addAssisgnedSubject(int uid, int sjid) {
		connection =  DB_Connection.getIsntance().getConnection();
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			sqlr = connection
					.prepareStatement("INSERT INTO assigned_subjects ( uid , sjid) VALUES (?, ?)");
			sqlr.setInt(1, uid);
			sqlr.setInt(2, sjid);
			int number = sqlr.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sqlr.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public boolean delByUid(int uid){
			PreparedStatement delpd = null;
			boolean result = false;
			Connection connection = DB_Connection.getIsntance().getConnection();
			try {
				
				delpd = connection.prepareStatement("DELETE FROM assigned_subjects  WHERE uid = ? ");
				delpd.setInt(1, uid);
				int number = delpd.executeUpdate();
				if (number > 0) {
					result = true;
				}
				delpd.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			
			}
			return  result;
		}
	public boolean delBySjid(int sjid){
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {
			
			delpd = connection.prepareStatement("DELETE FROM assigned_subjects  WHERE sjid = ? ");
			delpd.setInt(1, sjid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
	public boolean del(int uid , int sjid){
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {
			
			delpd = connection.prepareStatement("DELETE FROM assigned_subjects  WHERE sjid = ? AND uid = ? ");
			delpd.setInt(1, sjid);
			delpd.setInt(2, uid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return  result;
	}
	public ArrayList<AssignedSubjects> getByUid(int u){
		PreparedStatement sqls = null;
		ArrayList<AssignedSubjects> result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "SELECT uid , sjid FROM assigned_subjects WHERE uid = ? ORDER BY sjid ASC ";
			sqls = connection.prepareStatement(sql);
			sqls.setInt(1, u);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				int uid = rs.getInt(1);
				int sjid = rs.getInt(2);
				result.add(new AssignedSubjects(0, uid, sjid));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<AssignedSubjects> getBySjid(int s){
		PreparedStatement sqls = null;
		ArrayList<AssignedSubjects> result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "SELECT uid , sjid FROM assigned_subjects WHERE sjid = ? ORDER BY uid ASC ";
			sqls = connection.prepareStatement(sql);
			sqls.setInt(1, s);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				int uid = rs.getInt(1);
				int sjid = rs.getInt(2);
				result.add(new AssignedSubjects(0, uid, sjid));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean isExitUidInTable(int uid){
		PreparedStatement sql = null;
		boolean result = false;
		connection =  DB_Connection.getIsntance().getConnection();
		try {
			sql = connection.prepareStatement("SELECT uid FROM assigned_subjects where uid = ? ");
			sql.setInt(1, uid);
			ResultSet rs = sql.executeQuery();
			while (rs.next() == true) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return result;
	}
	public boolean isExitSjidInTable(int sjid){
		PreparedStatement sql = null;
		boolean result = false;
		connection =  DB_Connection.getIsntance().getConnection();
		try {
			sql = connection.prepareStatement("SELECT sjid FROM assigned_subjects where sjid = ? ");
			sql.setInt(1, sjid);
			ResultSet rs = sql.executeQuery();
			while (rs.next() == true) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return result;
	}
}
